package org.zf.database.server.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.lang.PatternPool;
import cn.hutool.core.util.EnumUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.zf.database.consts.DBPrivsType;
import org.zf.database.consts.DBType;
import org.zf.database.consts.FieldType;
import org.zf.database.domain.*;
import org.zf.database.exception.NotSupportException;
import org.zf.database.server.AbstractCommonDBServer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

@Slf4j
public class OracleDBServerImpl extends AbstractCommonDBServer
{
    private final Map<FieldType, List<String>> TYPEMAP = new HashMap<FieldType, List<String>>()
    {{
        put(FieldType.INT, Arrays.asList("INT,INTEGER,".split(",")));
        put(FieldType.NUMBER, Arrays.asList("NUMBER,FLOAT,DECIMAL,".split(",")));
        put(FieldType.VARCHAR, Arrays.asList("CHAR,VARCHAR,VARCHAR2,NCHAR,NVARCHAR,NVARCHAR2".split(",")));
        put(FieldType.BOOLEAN, Arrays.asList("".split(",")));
        put(FieldType.DATE, Arrays.asList("DATE".split(",")));
        put(FieldType.TIMESTAMP, Arrays.asList("TIMESTAMP".split(",")));
        put(FieldType.CLOB, Arrays.asList("CLOB,NCLOB".split(",")));
        put(FieldType.BLOB, Arrays.asList("BLOB,BFILE,ROWID,XMLTYPE".split(",")));
    }};

    @Getter
    private final Map<FieldType, String> DEFAULTMAP = new HashMap<FieldType, String>()
    {{
        put(FieldType.INT, "INT");
        put(FieldType.NUMBER, "NUMBER");
        put(FieldType.VARCHAR, "VARCHAR2({})");
        put(FieldType.BOOLEAN, "BIT");
        put(FieldType.DATE, "DATE");
        put(FieldType.TIMESTAMP, "TIMESTAMP");
        put(FieldType.CLOB, "CLOB");
        put(FieldType.BLOB, "BLOB");
    }};

    @Override
    public List<TableSpaceInfo> getTableSpace(DBInfo dbInfo) throws Exception
    {
        List<TableSpaceInfo> list = new ArrayList<>();
        try (
                Connection conn = getConn(dbInfo);
                Statement sm = conn.createStatement();
                ResultSet rs = sm.executeQuery("SELECT a.tablespace_name, total, free FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name");
        )
        {
            while (rs.next())
            {
                TableSpaceInfo tmp = new TableSpaceInfo();
                tmp.setSpaceName(rs.getString(1));
                tmp.setTotal(rs.getLong(2));
                tmp.setFree(rs.getLong(3));
                tmp.setUsed(tmp.getTotal() - tmp.getUsed());
                list.add(tmp);
            }
        }
        return list;
    }

    @Override
    public List<String> user(DBInfo dbInfo) throws Exception
    {
        List<String> user = super.user(dbInfo);
        if (CollUtil.isEmpty(user))
        {
            Set<String> userSet = new HashSet<>();
            try
            {
                Connection conn = getConn(dbInfo);
                Statement sm = conn.createStatement();
                ResultSet rs = sm.executeQuery("SELECT USERNAME FROM ALL_USERS ORDER BY USERNAME");
                while (rs.next())
                {
                    userSet.add(rs.getString(1));
                }
                return new ArrayList<>(userSet);
            } catch (Exception e)
            {
                log.error("查询异常", e);
                throw e;
            }
        }
        return user;
    }

    @Override
    public List<TableInfo> table(DBInfo dbInfo) throws Exception
    {
        List<TableInfo> table = super.table(dbInfo);
        if (CollUtil.isNotEmpty(table))
            return table;
        try (Connection conn = getConn(dbInfo);
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(StrUtil.format("SELECT t1.table_name, t2.comments, 1 FROM all_tables t1 LEFT JOIN all_tab_comments t2 ON t1.table_name = t2.table_name AND t1.owner = t2.owner WHERE t1.table_name NOT IN('REDO_LOG_TABLE','REDO_LOG_TABLE_CACHE') AND t1.owner = '{}' UNION SELECT t1.view_name, t2.comments, 0 FROM all_views t1 LEFT JOIN all_tab_comments t2 ON t1.view_name = t2.table_name AND t1.owner = t2.owner WHERE  t1.owner = '{}' UNION SELECT synonym_name,'', 0 FROM ALL_SYNONYMS WHERE owner = '{}'", dbInfo.getOwner(), dbInfo.getOwner(), dbInfo.getOwner()));
        )
        {
            table = new ArrayList<>();
            while (rs.next())
            {
                table.add(new TableInfo()
                {{
                    setDbInfo(dbInfo);
                    setTableName(rs.getString(1));
                    setComment(rs.getString(2));
                    setIsTable(rs.getBoolean(3));
                }});
            }
        } catch (Exception e)
        {
            log.error("查询错误", e);
            throw e;
        }
        return table;
    }

    @Override
    public TablePrivsInfo tablePrivs(TableInfo tableInfo) throws Exception
    {
        TablePrivsInfo tablePrivsInfo = new TablePrivsInfo();
        tablePrivsInfo.setTableInfo(tableInfo);
        try (Connection conn = getConn(tableInfo.getDbInfo());
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(StrUtil.format("SELECT PRIVILEGE FROM (SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='{}' UNION SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='{}' )) WHERE PRIVILEGE LIKE '%TABLE'", tableInfo.getDbInfo().getOwner(), tableInfo.getDbInfo().getOwner()));
        )
        {
            Map<DBPrivsType, Boolean> map = new HashMap<>();
            LinkedHashMap<String, DBPrivsType> enumMap = EnumUtil.getEnumMap(DBPrivsType.class);
            while (rs.next())
            {
                for (Map.Entry<String, DBPrivsType> stringDBPrivsTypeEntry : enumMap.entrySet())
                {
                    if (rs.getString(1).startsWith(stringDBPrivsTypeEntry.getKey()) || (rs.getString(1).startsWith("DROP") && stringDBPrivsTypeEntry.getValue() == DBPrivsType.TRUNCATE))
                        map.put(stringDBPrivsTypeEntry.getValue(), true);
                }
            }
            try (ResultSet userRs = sm.executeQuery(StrUtil.format("SELECT PRIVILEGE FROM USER_TAB_PRIVS WHERE OWNER = '{}' AND TABLE_NAME='{}'", tableInfo.getDbInfo().getOwner(), tableInfo.getTableName()));
            )
            {
                while (userRs.next())
                {
                    for (Map.Entry<String, DBPrivsType> stringDBPrivsTypeEntry : enumMap.entrySet())
                    {
                        if (rs.getString(1).startsWith(stringDBPrivsTypeEntry.getKey()))
                            map.put(stringDBPrivsTypeEntry.getValue(), true);
                    }
                }
            }
            tablePrivsInfo.setPrivs(map);
            return tablePrivsInfo;
        } catch (Exception e)
        {
            log.error("查询错误", e);
            throw e;
        }
    }

    @Override
    public Map<DBPrivsType, Boolean> dbPrivs(DBInfo dbInfo) throws Exception
    {
        Map<DBPrivsType, Boolean> res = new HashMap<>();
        try (Connection conn = getConn(dbInfo);
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery("SELECT PRIVILEGE FROM ROLE_SYS_PRIVS");
        )
        {
            while (rs.next())
            {
                String priv = rs.getString(1);
                if (priv.startsWith("DROP") && priv.endsWith("TABLE"))
                    res.put(DBPrivsType.DROP, true);
                if (priv.startsWith("CREATE") && priv.endsWith("TABLE"))
                    res.put(DBPrivsType.CREATE, true);
            }
        } catch (Exception e)
        {
            log.error("查询错误", e);
            throw e;
        }
        return res;
    }

    @Override
    public TableInfo tableCount(TableInfo tableInfo) throws Exception
    {
        tableInfo.setDataSize(countTable(tableInfo));
        try (Connection conn = getConn(tableInfo.getDbInfo());
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(StrUtil.format("SELECT * FROM (SELECT sum(bytes) datasize FROM dba_segments WHERE owner = '%s' AND segment_name = '%s'),(SELECT sum(bytes) indexsize FROM dba_segments WHERE owner || segment_name IN (SELECT owner || index_name FROM dba_indexes WHERE table_owner = '%s' AND table_name = '%s'))", tableInfo.getDbInfo().getOwner(), tableInfo.getTableName(), tableInfo.getDbInfo().getOwner(), tableInfo.getTableName()));
        )
        {
            if (rs.next())
            {
                tableInfo.setRowSize(rs.getInt(1));
                tableInfo.setIndexSize(rs.getLong(2));
                return tableInfo;
            }
        }
        return tableInfo;
    }

    @Override
    public List<FieldInfo> cols(TableInfo tableInfo) throws Exception
    {
        List<FieldInfo> cols = super.cols(tableInfo);
        if (CollUtil.isNotEmpty(cols))
            return cols;
        try (Connection conn = getConn(tableInfo.getDbInfo());
             Statement sm = conn.createStatement();
             ResultSet rs = sm.executeQuery(StrUtil.format("SELECT t1.column_name, t1.data_type, t1.data_length, t1.nullable, t2.comments, t1.data_scale, t3.constraint_name, t3.position FROM ( all_TAB_COLS t1 LEFT JOIN all_col_comments t2 ON t1.OWNER = t2.OWNER AND t1.table_name = t2.table_name AND t1.column_name = t2.column_name ) LEFT JOIN ( SELECT col.OWNER as owner, col.table_name as table_name, col.column_name as column_name, col.constraint_name as constraint_name, col.position as position FROM all_constraints con INNER JOIN all_cons_columns col ON con.constraint_name = col.constraint_name AND con.constraint_type = 'P' ) t3 ON t1.OWNER = t3.OWNER AND t1.table_name = t3.table_name AND t1.column_name = t3.column_name WHERE t1.OWNER = '{}' AND t1.table_name = '{}' ORDER BY t1.column_id", tableInfo.getDbInfo().getOwner(), tableInfo.getTableName()));
        )
        {
            cols = new ArrayList<>();
            while (rs.next())
            {
                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.setTableInfo(tableInfo);
                fieldInfo.setFieldName(rs.getString(1));
                fieldInfo.setSourceType(rs.getString(2));
                fieldInfo.setLength(rs.getInt(3));
                fieldInfo.setNullable("Y".equals(rs.getString(4)));
                fieldInfo.setComment(rs.getString(5));
                fieldInfo.setPrecision(Convert.toInt(rs.getString(6), 0));
                fieldInfo.setCommonType(fieldInfo.getLength() > 4000 && convertFieldType(fieldInfo.getSourceType()) == FieldType.VARCHAR ? FieldType.CLOB : convertFieldType(fieldInfo.getSourceType()));
                fieldInfo.setIsPK(StrUtil.isNotEmpty((rs.getString(7))));
                fieldInfo.setPkInd(Convert.toInt(rs.getString(8), null));
                cols.add(fieldInfo);
            }
        } catch (Exception e)
        {
            log.error("查询错误", e);
            throw e;
        }
        return cols;
    }

    @Override
    public DBInfo url2DBInfo(String jdbcUrl)
    {
        if (jdbcUrl.indexOf("?") == -1)
        {
            jdbcUrl += "?";
        }
        List<String> allGroups = ReUtil.getAllGroups(PatternPool.get("jdbc:oracle:thin:@//*(.*?):(.*?)[/:](.*?)\\?(.*)", 32), jdbcUrl, false);
        ConnInfo connInfo = new ConnInfo();
        connInfo.setIp(allGroups.get(0));
        connInfo.setPort(NumberUtil.parseInt(allGroups.get(1)));
        connInfo.setDbName(allGroups.get(2));
        connInfo.setAttr(allGroups.get(3));
        DBInfo dbInfo = new DBInfo();
        dbInfo.setConnInfo(connInfo);
        dbInfo.setJdbcUrl(jdbcUrl);
        dbInfo.setDbType(DBType.ORACLE);
        return dbInfo;
    }

    @Override
    public String dbInfo2Url(DBInfo dbInfo)
    {
        ConnInfo connInfo = dbInfo.getConnInfo();
        if (connInfo == null)
            throw new NoSuchElementException("未找到连接配置信息");
        return StrUtil.isEmpty(connInfo.getAttr()) ? StrUtil.format("jdbc:oracle:thin:@//{}:{}/{}", connInfo.getIp(), connInfo.getPort(), connInfo.getDbName()) : StrUtil.format("jdbc:oracle:thin:@//{}:{}/{}?{}", connInfo.getIp(), connInfo.getPort(), connInfo.getDbName(), connInfo.getAttr());
    }

    @Override
    public FieldType convertFieldType(String type) throws NotSupportException
    {
        return super.convertFieldType(TYPEMAP, type);
    }

    @Override
    public String escapeCharacter(String sql)
    {
        return sql.replace("'", "''");
    }


    @Override
    public String updateColumnSql(FieldInfo fieldInfo) throws NotSupportException
    {
        String dbName = fieldInfo.getTableInfo().getDbInfo().getOwner();
        String tableName = fieldInfo.getTableInfo().getTableName();
        String colName = fieldInfo.getFieldName();
        String defaults = fieldInfo.getDefaultStr();
        if (!getDEFAULTMAP().containsKey(fieldInfo.getCommonType()))
            throw new NotSupportException("不支持的字段类型:" + fieldInfo.getCommonType().getType());
        return StrUtil.format("ALTER TABLE {}.{} MODIFY {} {} {} {}", dbName, tableName, colName, getTypeSql(fieldInfo), StrUtil.isBlank(defaults) ? " " : " DEFAULT " + defaults, StrUtil.isEmpty(fieldInfo.getComment()) ? " " : StrUtil.format("COMMENT '{}'", fieldInfo.getComment()));
    }

    @Override
    public String funD2C(String fieldName, boolean dateOrTimestamp)
    {
        return dateOrTimestamp ? StrUtil.format("to_char({}, 'yyyy-mm-dd hh24:mi:ss.ff')", fieldName) : StrUtil.format("to_char(to_timestamp({}), 'yyyy-mm-dd hh24:mi:ss.ff')", fieldName);
    }
}
